from fastapi import APIRouter, HTTPException, Body, Query, Depends
from typing import Optional, Dict
import time
from bson import ObjectId
import math

from ..models.db_info_setting import DBInfoSettingModel
from ..schemas.db_info_setting import (
    DBInfoSettingCreate, 
    DBInfoSettingUpdate,
    DBInfoSettingInDB
)
from ..utils.response_utils import success_response, error_response
from ..utils.db_utils import test_database_connection, DatabaseConnectionError, get_database_tables, get_table_columns
from ..utils.crypt_utils import decrypt_password
from ..routers.auth import get_current_user  # 引入用户认证依赖

router = APIRouter(prefix="/dbInfoSetting")

@router.post("/create")
async def create_db_info_setting(
    db_info: DBInfoSettingCreate = Body(...),
    current_user = Depends(get_current_user)  # 添加当前用户依赖
):
    """
    创建数据库配置信息
    """
    try:
        # 转换为字典
        db_info_dict = db_info.dict()
        info_db = await DBInfoSettingModel.find_by_connection_name(db_info.connection_name)
        if info_db:
            return error_response(message="连接名称已存在", code=404)
        # 创建记录
        result = await DBInfoSettingModel.create(db_info_dict,current_user.username)
        
        return success_response(data=result)
    except Exception as e:
        return error_response(message=f"创建数据库配置失败: {str(e)}", code=500)

@router.post("/update")
async def update_db_info_setting(
    id: str = Body(..., embed=True),
    db_info: DBInfoSettingUpdate = Body(...),
    current_user = Depends(get_current_user)  # 添加当前用户依赖
):
    """
    更新数据库配置信息
    """
    try:

        # 检查记录是否存在
        existing = await DBInfoSettingModel.find_by_id(id)
        if not existing:
            return error_response(message="未找到指定ID的数据库配置信息", code=404)
        info_db = await DBInfoSettingModel.find_by_connection_name(db_info.connection_name,id)
        if info_db:
            return error_response(message="连接名称已存在", code=404)
        
        # 转换为字典并过滤掉None值
        db_info_dict = {k: v for k, v in db_info.dict().items() if v is not None}
        
        # 更新记录
        result = await DBInfoSettingModel.update(id, db_info_dict,current_user.username )
        
        if result:
            return success_response(data=result)
        else:
            return error_response(message="更新数据库配置失败", code=500)
    except Exception as e:
        return error_response(message=f"更新数据库配置失败: {str(e)}", code=500)

@router.get("/list")
async def list_db_info_settings(
    db_type: Optional[str] = Query(None),
    connection_name: Optional[str] = Query(None),
    host: Optional[str] = Query(None),
    page: int = Query(1, ge=1),  # 页码，默认为1，必须大于等于1
    size: int = Query(10, ge=1, le=100),  # 每页数量，默认为10，范围1-100
    current_user = Depends(get_current_user)  # 添加当前用户依赖
):
    """
    查询数据库配置信息列表，支持分页
    """
    try:
        # 构建查询条件
        query = {}
        if db_type:
            query["db_type"] = db_type
        if connection_name:
            query["connection_name"] = connection_name
        if host:
            query["host"] = host
            
        # 计算总记录数
        total = await DBInfoSettingModel.count(query)
        
        # 计算分页
        skip = (page - 1) * size
        
        # 查询记录
        results = await DBInfoSettingModel.find_all(query, skip=skip, limit=size)
        
        return success_response(
            data={
                "list": results,
                "total": total,
                "page": page,
                "size": size
            }
        )
    except Exception as e:
        return error_response(message=f"查询数据库配置列表失败: {str(e)}", code=500)

@router.get("/all_list")
async def all_list_db_info_settings(
    current_user = Depends(get_current_user)  # 添加当前用户依赖
):
    """
    查询所有数据库配置信息列表
    """
    try:
        # 查询记录
        results = await DBInfoSettingModel.find_all()
        return success_response( data=results)
    except Exception as e:
        return error_response(message=f"查询数据库配置列表失败: {str(e)}", code=500)



@router.get("/info")
async def get_db_info_setting(
    id: str = Query(...)
):
    """
    根据ID查询数据库配置信息
    """
    try:
        # 查询记录
        result = await DBInfoSettingModel.find_by_id(id)
        
        if result:
            return success_response(data=result)
        else:
            return error_response(message="未找到指定ID的数据库配置信息", code=404)
    except Exception as e:
        return error_response(message=f"查询数据库配置信息失败: {str(e)}", code=500)

@router.post("/Connection_testing")
async def test_db_connection(
    db_info: DBInfoSettingCreate = Body(...)
):
    """
    测试数据库连接
    """
    try:
        # 转换为字典
        db_info_dict = db_info.dict()
        
        # 测试连接
        try:
            # 如果是来自表单的直接测试，密码已经是明文，无需解密
            # 如果是来自数据库的测试，则需要先解密
            if db_info_dict.get("id"):
                # 从数据库获取配置
                stored_config = await DBInfoSettingModel.find_by_id(db_info_dict["id"])
                if stored_config:
                    # 解密密码
                    decrypted_config = DBInfoSettingModel.decrypt_db_info(stored_config)
                    db_info_dict = decrypted_config
                    
            # 测试数据库连接
            test_database_connection(
                db_type=db_info_dict["db_type"],
                host=db_info_dict["host"],
                port=db_info_dict["port"],
                user=db_info_dict["username"],
                password=db_info_dict["password"],
                database=db_info_dict["database_name"],
                extra_params=db_info_dict.get("connection_string_ext")
            )
            
            return success_response(data={"connected": True}, message="连接测试成功")
        except DatabaseConnectionError as e:
            return error_response(message=f"连接测试失败: {str(e)}", code=400)
    except Exception as e:
        return error_response(message=f"连接测试失败: {str(e)}", code=500)

@router.post("/delete")
async def delete_db_info_setting(
    id: str = Body(..., embed=True)
):
    """
    删除数据库配置
    """
    try:
        existing = await DBInfoSettingModel.find_by_id(id)
        if not existing:
            return error_response(message="未找到指定ID的数据库配置信息", code=404)
        await DBInfoSettingModel.collection.delete_one({"_id": ObjectId(existing["_id"])})
        return success_response(message="删除成功")
    except Exception as e:
        return error_response(message=f"删除数据库配置失败: {str(e)}", code=500)

@router.get("/tables")
async def get_database_tables_api(
    id: str = Query(...)
):
    """
    获取数据库中的表列表
    
    Args:
        id: 数据库配置ID
    
    Returns:
        表名列表
    """
    try:
        # 查询数据库配置
        db_config = await DBInfoSettingModel.find_by_id(id)
        
        if not db_config:
            return error_response(message="未找到指定ID的数据库配置信息", code=404)
        
        # 解密密码
        decrypted_config = DBInfoSettingModel.decrypt_db_info(db_config)
        
        # 获取表列表
        tables = get_database_tables(
            db_type=decrypted_config["db_type"],
            host=decrypted_config["host"],
            port=decrypted_config["port"],
            user=decrypted_config["username"],
            password=decrypted_config["password"],
            database=decrypted_config["database_name"],
            extra_params=decrypted_config.get("connection_string_ext")
        )
        
        return success_response(data=tables)
    except Exception as e:
        return error_response(message=f"获取数据库表列表失败: {str(e)}", code=500)

@router.get("/columns")
async def get_table_columns_api(
    id: str = Query(...),
    table_name: str = Query(...),
):
    """
    获取表中的字段列表
    
    Args:
        id: 数据库配置ID
        table_name: 表名
        
    Returns:
        字段名列表
    """
    try:
        # 查询数据库配置
        db_config = await DBInfoSettingModel.find_by_id(id)
        
        if not db_config:
            return error_response(message="未找到指定ID的数据库配置信息", code=404)
        
        # 解密密码
        decrypted_config = DBInfoSettingModel.decrypt_db_info(db_config)
        
        # 获取字段列表
        columns = get_table_columns(
            db_type=decrypted_config["db_type"],
            host=decrypted_config["host"],
            port=decrypted_config["port"],
            user=decrypted_config["username"],
            password=decrypted_config["password"],
            database=decrypted_config["database_name"],
            table_name=table_name,
            extra_params=decrypted_config.get("connection_string_ext")
        )
        
        return success_response(data=columns)
    except Exception as e:
        return error_response(message=f"获取表字段列表失败: {str(e)}", code=500)